﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace DataClassLayer
{
    public class NationalStatisticCollection
    {
        public NationalStatisticCollection()
        {
            
        }
        public bool Add(NationalStatistic nationalstatistic)
        {
            try
            {
                List<string> query = new List<string>();
                string command = @"Insert into SOCCER_STATISTICS_TEAMS (Win,Draw,Lose,NationalTeamID,Year) Values
                   (" + nationalstatistic.Win + ","
                      + nationalstatistic.Draw + ","
                      + nationalstatistic.Lose + ","
                      + nationalstatistic.NationalTeamID + ","
                   + nationalstatistic.Year + ")";
                query.Add(command);
                DataAccessObject.DataBase.CreateUpdateDelete(query);
                return true;
            }
            catch
            {
                return false;
            }
        }
        public bool Edit(NationalStatistic nationalstatistic)
        {
            try
            {
                List<string> query = new List<string>();
                string command = @"Update SOCCER_STATISTICS_TEAMS Set 
                      Win=" + nationalstatistic.Win +
                     ",Draw=" + nationalstatistic.Draw +
                     ",Lose=" + nationalstatistic.Lose +
                     ",NationalTeamID=" + nationalstatistic.NationalTeamID +
                     ",Year=" + nationalstatistic.Year + " Where ID=" + nationalstatistic.ID;
                query.Add(command);
                DataAccessObject.DataBase.CreateUpdateDelete(query);
                return true;
            }
            catch
            {
                return false;
            }
        }
        public bool Delete(NationalStatistic nationalstatistic)
        {
            try
            {
                List<string> query = new List<string>();
                string command = @"Delete From SOCCER_STATISTICS_TEAMS Where ID= " + nationalstatistic.ID; 
                query.Add(command);
                DataAccessObject.DataBase.CreateUpdateDelete(query);
                return true;
            }
            catch
            {
                return false;
            }
        }
        public DataTable FillList(NationalStatistic nationalstatistic)
        {
            try
            {
                string wherestatement = WhereStatement(nationalstatistic);
                string command = @"SELECT        SOCCER_STATISTICS_TEAMS.ID, SOCCER_STATISTICS_TEAMS.Win, SOCCER_STATISTICS_TEAMS.Draw, SOCCER_STATISTICS_TEAMS.Lose, 
                                   SOCCER_STATISTICS_TEAMS.NationalTeamID, SOCCER_STATISTICS_TEAMS.Year, SOCCER_NATIONAL_TEAM_INFO.Name AS NationalTeamName
FROM                               SOCCER_STATISTICS_TEAMS INNER JOIN
                                   SOCCER_NATIONAL_TEAM_INFO ON SOCCER_STATISTICS_TEAMS.NationalTeamID = SOCCER_NATIONAL_TEAM_INFO.ID";
                if (wherestatement != null &&
                    wherestatement != "")
                {
                    command += " Where " + wherestatement;
                }
                DataTable table = DataAccessObject.DataBase.ExcuteQuery(command);
                return table;
            }
            catch
            {
                return null;
            }
        }
        public int MaxID()
        {
            try
            {
                int Maxid = 1;
                string command = @"SELECT    Max(ID)  AS MaxID FROM   SOCCER_STATISTICS_TEAMS";
                DataTable table = DataAccessObject.DataBase.ExcuteQuery(command);
                if (table.Rows.Count == 1)
                {
                    Maxid = int.Parse(table.Rows[0]["MaxID"].ToString());
                }
                return Maxid;
            }
            catch
            {
                return 1;
            }
        }
        public string WhereStatement(NationalStatistic nationalstatistic)
        {
            string wherestatement = "";
            if (nationalstatistic.ID > 0)
            {
                if (wherestatement != null &&
                    wherestatement != "")
                {
                    wherestatement += "And SOCCER_STATISTICS_TEAMS.ID= " + nationalstatistic.ID;
                }
                else
                {
                    wherestatement += "SOCCER_STATISTICS_TEAMS.ID= " + nationalstatistic.ID;
                }
            }
            if (nationalstatistic.Year > 0)
            {
                if (wherestatement != null &&
                    wherestatement != "")
                {
                    wherestatement += "And SOCCER_STATISTICS_TEAMS.Year= " + nationalstatistic.Year;
                }
                else
                {
                    wherestatement += "SOCCER_STATISTICS_TEAMS.Year= " + nationalstatistic.Year;
                }
            }
            if (nationalstatistic.NationalTeamID > 0)
            {
                if (wherestatement != null &&
                    wherestatement != "")
                {
                    wherestatement += "And SOCCER_STATISTICS_TEAMS.NationalTeamID= " + nationalstatistic.NationalTeamID;
                }
                else
                {
                    wherestatement += "SOCCER_STATISTICS_TEAMS.NationalTeamID= " + nationalstatistic.NationalTeamID;
                }
            }
            return wherestatement;
        }
    }
}
